﻿create database HospitalManagement;
go
use HospitalManagement;
go

create schema Form
go
create schema HumanResources;
go
create schema HospitalRecords;
go
create schema Equipment;
go
create schema Payment;
go
create schema Patient;
go
create schema Department;
go
-- Bảng loại thanh toán --
create table Payment.Payment
(
PaymentID int not null identity(1,1) constraint PK_PaymentID primary key(PaymentID),
PaymentMethod nvarchar(255) Not Null,
)
go
-- Bảng mẫu loại mẫu giấy phiếu --
create table Form.Papers
(
PapersID int not null identity(1,1) constraint PK_paperID primary key(PapersID), 
Name nvarchar(255) Not Null,
)
go
-- Bảng loại mẫu số chuyên môn
create table Form.ProPapers
(
ProPaperID int not null identity(1,1) constraint PK_ProPaperID primary key(ProPaperID),
Name nvarchar(255) Not Null
)
go
-- Bảng bảo hiểm -- 
create table Patient.Insurance
(
InsuranceID int not null identity(1,1) constraint PK_InsuranceID primary key(InsuranceID),
NameInsurance nvarchar(255) Not Null,
Cardnumber nvarchar(50) Not Null,
ExpirationDate DateTime Not Null,
)
go
-- Bảng loại tổ chức -
create table Department.TypeOrganization
(
TypeOrganizationID int not null identity(1,1) constraint PK_TypeOrganizationID primary key(TypeOrganizationID),
NameTypeOrganization nvarchar(255) Not Null,
)
go
-- Bảng chi tiết loại tổ chức --
create table Department.OrganizationDetails
(
OrganizationDetailsID int not null identity(1,1) constraint PK_OrganizationDetailsID primary key(OrganizationDetailsID),
NameDetails nvarchar(255) not null ,
TypeOrganizationID int not null constraint Fk_TypeOrganizationID foreign key references Department.TypeOrganization (TypeOrganizationID) on delete cascade
)
go
-- Bảng loại chức vụ --
create table Department.Positions
(
PositionsID int not null identity(1,1) constraint Pk_positionsID primary key(positionsID),
NamePositions nvarchar(255),
)
go
-- Bảng loại phòng ---
create table Equipment.RoomType
(
RoomNo int not null identity(1,1) constraint PK_RoomNo primary key(RoomNo),
RoomName nvarchar(255) Not Null,
FloorNo nvarchar(255) not null,
OrganizationDetailsID int not null constraint Fk_OrganizationDetailsID foreign key references Department.OrganizationDetails(OrganizationDetailsID)on delete cascade
)
go
-- Bảng số giường bệnh --
create table Equipment.Beds
(
BedsID int not null identity(1,1) constraint PK_BedsID primary key(BedsID),
Amout Money Not Null,
RoomNo int not null constraint Fk_RoomNo foreign key references Equipment.RoomType(RoomNo) on delete cascade
)
go
-- Bảng loại thuốc --
Create Table Equipment.DrugType
(
	DrugTypeID Int Not Null Identity(1,1) Constraint PK_DrugTypeID Primary Key (DrugTypeID),
	DrugTypeName Nvarchar(255) Not Null,
)
go
-- Bảng chi tiết thuốc 
Create Table Equipment.DrugDetails
(
	DrugID Int Not Null Identity(1,1) Constraint PK_DrugID Primary Key (DrugID),
	DrugName Nvarchar(255) Not Null,
	Unit Nvarchar(255) Not Null,
	Price Money Not Null,
	Quantity Int Not Null,
	Source Nvarchar(Max) Not Null,
	ProductionDate DateTime Not Null,
	UseTime DateTime Not Null,
	UseGuide Nvarchar(Max) Not Null,
	AddedWareHouseDate DateTime Not Null,
	DrugTypeID Int Not Null Constraint FK_DrugTypeID Foreign Key References Equipment.DrugType(DrugTypeID),
)
go
-- Bảng loại nhân viên --
create table HumanResources.TypeEmployee
(
TypeEmployeesID int not null identity(1,1) constraint PF_TypeEmployeesID Primary key(TypeEmployeesID),
NameTypeEmployees nvarchar(50),
)
go
-- Bảng loại bác sỹ --
create table HumanResources.TypeDoctor
(
TypeDoctorID  int not null identity(1,1) constraint PF_KindOfDoctorID Primary key(TypeDoctorID),
NameTypeDoctor nvarchar(50),
TypeEmployeesID int not null constraint FK_TypeEmployeesID foreign key references HumanResources.TypeEmployee (TypeEmployeesID) on delete cascade      
)
go

-- Bảng các ban nghành --
--create table Department.Department
--(
--DepartmentID int not null identity(1,1) constraint PK_DepartmentID primary key(DepartmentID),
--DepartmentName nvarchar(50) Not Null,
--)

-- Bảng chi tiết nhân viên --
create table HumanResources.Employee
(
EmployeeID int not null identity(1,1) constraint PK_EmployeeID primary key(EmployeeID),
NameEmployee nvarchar(50),
Address nvarchar(50),
BirthDay datetime,
Gender bit,
Identification int,
PhoneNumber int constraint ck_PhoneNumber check(PhoneNumber Like '[0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9]'),
Email nvarchar(50),
Salary Money Not Null, 
StartDate datetime,
EndDate  datetime, 
TypeEmployeesID int not null constraint FK_TypeEmployeeID foreign key references HumanResources.TypeEmployee(TypeEmployeesID)on delete cascade,       
PositionsID int not null constraint Fk_PositionsID foreign key references Department.Positions (PositionsID) on delete cascade,
OrganizationDetailsID int not null constraint Fk_OrganizationDetailsID foreign key references Department.OrganizationDetails(OrganizationDetailsID)on delete cascade,                          
)
go
-- Bảng chấm công --
create table HumanResources.Timekeepings
(
TimekeepingID int not null identity(1,1) constraint PK_TimekeepingID primary key(TimekeepingID),
EmployeeID int not null constraint FK_EmployeeID foreign key references HumanResources.Employee(EmployeeID)on delete cascade,        
WorkingDate DateTime,
WorkingTime int,                         
)
go
-- Bảng trả lương --
create table Payment.Payroll
(
BillID int identity(1,1) Not Null Constraint PK_BillID Primary Key(BillID),
TimekeepingID int not null constraint FK_TimekeepingID foreign key references HumanResources.Timekeepings(TimekeepingID),
EmployeeID int not null constraint FK_EmpID foreign key references HumanResources.Employee(EmployeeID)on delete cascade,
PaymentID int not null constraint Fk_PaymentID foreign key references Payment.Payment(PaymentID)on delete cascade,
TotalAmount nvarchar(20),
TotalDate int
)
go
-- Bảng đăng nhập hệ thống -
create table HumanResources.Login
(
LoginID int Identity(1,1) Not Null Constraint PK_LoginID Primary Key(LoginID),
UserName nvarchar(30),
PassWord nvarchar(30),
EmployeeID int not null constraint FK_EmployeesID foreign key references HumanResources.Employee(EmployeeID)on delete cascade,
)
go
-- Bảng loại bệnh án --
Create table Form.TypeMedical
(
	TypeMedicalID int Identity(1,1) Not Null Constraint PK_TypeMedicalID Primary Key(TypeMedicalID),
	NameMedical Nvarchar(255) Not Null,
)
go
--------------------create table Declaration.DetailPatients(chi tiết bệnh nhân)-------------
create table Patient.Details
(
PatientID int not null identity(1,1) constraint PK_PatientID primary key(PatientID),
Name nvarchar(50),
Address nvarchar(100),
Gender bit not null,
Birthday datetime ,
Occupation nvarchar(50),
AddressOccupation nvarchar(255),
Ethnic nvarchar(50),
ContactPerson nvarchar(50),
ContactAddress nvarchar (400),
ContactPhone int check(ContactPhone Like '[0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9]'),
Biography nvarchar(100),
States Nvarchar(255) Not Null,
TypePatient nvarchar(255),
TypeTreatment nvarchar(255),
PhoneNumber int constraint ck_phone check(PhoneNumber Like '[0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9]'),
AddressEmail nvarchar(255),
HospitalizedDate datetime not null,
DischargeDate datetime ,
BedsID int Constraint FK_BedsID foreign key references Equipment.Beds (BedsID) on delete cascade,
OrganizationDetailsID int not null constraint Fk_OrganizationDetailsID3 foreign key references Department.OrganizationDetails(OrganizationDetailsID),
InsuranceID int constraint FK_InsuranceID foreign key references Patient.Insurance (InsuranceID) on delete cascade,
)
go
Create Table Patient.TreatMent
(
	TreatMentID int Identity(1,1) Constraint PK_TreatMentID Primary Key (TreatMentID),
	PatientID  int not null constraint FK_PatientsID1  foreign key references Patient.Details(PatientID)on delete cascade,  
	DrugID  int not null constraint FK_DrugID  foreign key references Equipment.DrugDetails(DrugID)on delete cascade,
	EmployeeID int not null constraint FK_EmployeeID3 foreign key references HumanResources.Employee(EmployeeID),        
)
go
-- Bảng chi tiết loại mẫu giấy phiếu --
create table Form.PapersDetails
(
PapersDetailsID int not null identity(1,1) constraint PK_PapersDetailsID primary key(PapersDetailsID),
Name nvarchar(255) Not Null,
Content nvarchar(4000) Not Null,
PapersID int not null constraint FK_PaperID Foreign key references Form.Papers(PapersID) on delete cascade,
PatientID  int not null constraint FK_PatientID2  foreign key references Patient.Details(PatientID)on delete cascade,  
)
go
-- Bảng chi tiết loại mẫu số chuyên môn  --
create table Form.ProPapersDetails
(
ProPaperDetailsID int not null identity(1,1) constraint PK_DetailPropaperID primary key(ProPaperDetailsID),
Name nvarchar(255),
Content nvarchar(4000),
ProPaperID int not null  constraint FK_ProPaperID foreign key references Form.ProPapers(ProPaperID) on delete cascade,
PatientID  int not null constraint FK_PatientID3  foreign key references Patient.Details(PatientID)on delete cascade,  

)
go
-- Bảng chi tiết bệnh án --
create table Patient.DetailsMedical
(
DetailsMedicalID int not null identity(1,1) constraint PK_DetailsMedicalID primary key(DetailsMedicalID),
TypeMedicalID  int not null constraint FK_TypeMedicalID  foreign key references Form.TypeMedical(TypeMedicalID)on delete cascade,
PatientID  int not null constraint FK_PatientID  foreign key references Patient.Details(PatientID)on delete cascade,  
TreatMentID  int not null constraint FK_TreatMentID foreign key references Patient.TreatMent(TreatMentID),  
ContentMedical nvarchar(4000),
)
go
------------------create table Equipment.DetailsTypeDrug(chi tiết loại thuốc) -------
create table Equipment.DetailsTypeDrug
(
DetailsTypeDrugID int not null constraint Pk_DetailsTypeDrugID primary key(DetailsTypeDrugID),
Name nvarchar(50),
NumberOf int,
Unit int,
Origin nvarchar(100),
OrganizationDetailsID int not null constraint Fk_OrganizationDetailsID1 foreign key references Department.OrganizationDetails(OrganizationDetailsID)on delete cascade
)